package com.sfy.les.dictionary.oracle;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.sfy.les.db.DBConnection;
import com.sfy.les.dictionary.DataBaseDictionary;
import com.sfy.les.dictionary.DictionaryInfo;
import com.sfy.les.table.TableInfo;
import com.sfy.les.utils.Common;

public class OracleDictionaryInfo extends DictionaryInfo{
	
	@Override
	public String getQueryAllTablesInfoSql(DataBaseDictionary exportParam) {
		String querySql = "select TABLE_NAME,decode(table_type,'TABLE',comments,table_type) TABLE_DESC from user_tab_comments";
		if(!Common.checkNullOrEmpty(exportParam.getTableNamePrefix())){
			querySql += " WHERE TABLE_NAME like '" + exportParam.getTableNamePrefix() + "%'";
		}
		return querySql;
	}

	@Override
	public String getQueryTableFieldsInfoSql(DataBaseDictionary exportParam, TableInfo tableInfo) {
		String querySql = "";
		querySql = "SELECT USER_TAB_COLS.COLUMN_NAME  as FIELD_NAME,";
		querySql += " USER_TAB_COLS.DATA_TYPE    as FIELD_TYPE,";
		querySql += " USER_TAB_COLS.DATA_LENGTH  as FIELD_LENGTH,";
		querySql += " USER_TAB_COLS.DATA_PRECISION  as FIELD_DECIMAL_LENGTH,";
		querySql += " CASE USER_TAB_COLS.NULLABLE WHEN 'Y' THEN '允许' ELSE '不允许' END as FIELD_IS_EMPTY,";
		//querySql += " CASE uc.constraint_type WHEN 'P' THEN '是' ELSE '否' END FIELD_IS_PRIMARY_KEY,";
		querySql += " '/'  FIELD_IS_AUTOINCERE, ";
		querySql += " USER_TAB_COLS.data_default FIELD_DEFAULT_VALUE,";
		querySql += " user_col_comments.comments as FIELD_DESC";
		querySql += " FROM USER_TAB_COLS";
		querySql += " inner join user_col_comments";
		querySql += " on user_col_comments.TABLE_NAME = USER_TAB_COLS.TABLE_NAME";
		querySql += " and user_col_comments.COLUMN_NAME = USER_TAB_COLS.COLUMN_NAME";
		//querySql += " left join user_cons_columns ucc on ucc.table_name=user_col_comments.table_name and ucc.column_name=user_col_comments.column_name";
		//querySql += " left join user_constraints uc on uc.constraint_name = ucc.constraint_name and uc.constraint_type='P'";
		querySql += " where user_col_comments.TABLE_NAME = '" + tableInfo.getTableName() + "'";
		return querySql;
	}

	@Override
	public String getQueryTableIndexInfoSql(DataBaseDictionary exportParam, TableInfo tableInfo) {
		String querySql = "";
		querySql = "select user_indexes.INDEX_TYPE, '/' INDEX_STYLE, user_indexes.INDEX_NAME,LISTAGG(user_ind_columns.COLUMN_NAME,',') WITHIN GROUP (ORDER BY user_ind_columns.COLUMN_NAME) INDEX_FIELDS";
		querySql += " from user_indexes";
		querySql += " inner join user_ind_columns on user_ind_columns.index_name = user_indexes.INDEX_NAME AND user_ind_columns.TABLE_NAME = user_indexes.TABLE_NAME";
		querySql += " where user_indexes.table_name = '" + tableInfo.getTableName() + "'";
		querySql += " GROUP BY user_indexes.INDEX_TYPE,user_indexes.INDEX_NAME";
		return querySql;
	}

	@Override
	public String getTableDdl(DataBaseDictionary exportParam, TableInfo tableInfo) throws Exception {
		String querySql = "";
		String tableDesc = tableInfo.getTableDesc();
		String tableType = "TABLE";
		if("VIEW".equals(tableDesc)){
			tableType = "VIEW";
		}
		querySql = "select '" + tableInfo.getTableName() + "' TABLE_NAME, DBMS_METADATA.GET_DDL('" + tableType + "','" + tableInfo.getTableName() + "') TABLE_INIT_SQL from dual";
		Connection conn = null;
		Statement ps = null;
		ResultSet rs = null;
		//建表语句
		String ddl = "";
		try {
			conn = DBConnection.getConnection(exportParam);
			if(conn == null){
				throw new Exception("获取数据库连接失败！");
			}
			ps = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			rs = ps.executeQuery(querySql);
        	while (rs.next()) {
        		ddl = rs.getString(rs.getMetaData().getColumnName(2));
			}
		}catch (SQLException e) {
			throw new Exception(e);
		}finally{			
			DBConnection.closeConnection(conn, ps, rs);
		}
		return ddl;
	}
	
}
